// 数据库结构检查脚本
const mysql = require('mysql2/promise')
const config = require('./config/config')

async function checkDatabase() {
  console.log('\n========================================')
  console.log('🔍 检查数据库结构...')
  console.log('========================================\n')

  try {
    const connection = await mysql.createConnection({
      host: config.db.host,
      user: config.db.user,
      password: config.db.password,
      database: config.db.database
    })

    console.log('✅ 数据库连接成功\n')

    const issues = []

    // 1. 检查商品表是否有 brand 字段
    console.log('📋 检查 jd_products 表...')
    const [brandColumns] = await connection.execute(
      "SHOW COLUMNS FROM jd_products LIKE 'brand'"
    )
    
    if (brandColumns.length === 0) {
      console.log('❌ 缺少 brand 字段')
      issues.push({
        table: 'jd_products',
        issue: '缺少 brand 字段',
        fix: "ALTER TABLE jd_products ADD COLUMN brand VARCHAR(100) DEFAULT NULL COMMENT '品牌' AFTER category"
      })
    } else {
      console.log('✅ brand 字段存在')
    }

    // 2. 检查订单表的 status 字段类型
    console.log('\n📋 检查 jd_orders 表...')
    const [statusColumns] = await connection.execute(
      "SHOW COLUMNS FROM jd_orders LIKE 'status'"
    )
    
    if (statusColumns.length > 0) {
      const statusColumn = statusColumns[0]
      console.log(`   当前 status 字段类型: ${statusColumn.Type}`)
      console.log(`   当前默认值: ${statusColumn.Default}`)
      
      if (statusColumn.Type === 'tinyint') {
        console.log('⚠️  使用 TINYINT 类型（整数），最新标准是 VARCHAR(20)（字符串）')
        issues.push({
          table: 'jd_orders',
          issue: 'status 字段类型不一致',
          fix: "ALTER TABLE jd_orders MODIFY COLUMN status VARCHAR(20) DEFAULT 'pending' COMMENT '订单状态：pending-待付款，paid-待发货，shipped-待收货，completed-已完成，cancelled-已取消'"
        })
      } else {
        console.log('✅ status 字段类型正确')
      }
    }

    // 3. 检查所有表是否存在
    console.log('\n📋 检查所有必需的表...')
    const requiredTables = [
      'jd_users',
      'jd_categories', 
      'jd_products',
      'carts',
      'jd_addresses',
      'jd_orders',
      'jd_order_items'
    ]

    const [tables] = await connection.execute('SHOW TABLES')
    const existingTables = tables.map(t => Object.values(t)[0])

    for (const table of requiredTables) {
      if (existingTables.includes(table)) {
        console.log(`✅ ${table}`)
      } else {
        console.log(`❌ ${table} - 表不存在`)
        issues.push({
          table: table,
          issue: '表不存在',
          fix: '需要运行完整的数据库初始化脚本'
        })
      }
    }

    // 4. 显示所有商品表的字段
    console.log('\n📋 jd_products 表的完整字段列表:')
    const [productColumns] = await connection.execute('SHOW COLUMNS FROM jd_products')
    productColumns.forEach(col => {
      console.log(`   - ${col.Field} (${col.Type})`)
    })

    // 5. 检查是否有测试数据
    console.log('\n📊 数据统计:')
    const [userCount] = await connection.execute('SELECT COUNT(*) as count FROM jd_users')
    console.log(`   用户数: ${userCount[0].count}`)
    
    const [categoryCount] = await connection.execute('SELECT COUNT(*) as count FROM jd_categories')
    console.log(`   分类数: ${categoryCount[0].count}`)
    
    const [productCount] = await connection.execute('SELECT COUNT(*) as count FROM jd_products')
    console.log(`   商品数: ${productCount[0].count}`)
    
    const [orderCount] = await connection.execute('SELECT COUNT(*) as count FROM jd_orders')
    console.log(`   订单数: ${orderCount[0].count}`)

    await connection.end()

    // 总结
    console.log('\n========================================')
    if (issues.length === 0) {
      console.log('✅ 数据库结构完全正确！')
    } else {
      console.log(`⚠️  发现 ${issues.length} 个问题需要修复:\n`)
      issues.forEach((issue, index) => {
        console.log(`${index + 1}. 表: ${issue.table}`)
        console.log(`   问题: ${issue.issue}`)
        console.log(`   修复: ${issue.fix}\n`)
      })
    }
    console.log('========================================\n')

    return issues

  } catch (error) {
    console.error('❌ 检查失败：', error.message)
    process.exit(1)
  }
}

// 执行检查
checkDatabase()

